﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using OfficeOpenXml;
using System.IO;

namespace DidaManager.Models
{
    /// <summary>
    /// 环境监测参数数据模型
    /// </summary>
    public class MonitoringParameter
    {
        /// <summary>
        /// 编码
        /// </summary>
        public string Code { get; set; } = string.Empty;
        
        /// <summary>
        /// 中文名称
        /// </summary>
        public string ChineseName { get; set; } = string.Empty;
        
        /// <summary>
        /// 原编码
        /// </summary>
        public string OriginalCode { get; set; } = string.Empty;
        
        /// <summary>
        /// 缺省计量单位
        /// </summary>
        public string DefaultUnit { get; set; } = string.Empty;
        
        /// <summary>
        /// 缺省计量
        /// </summary>
        public string DefaultMeasurement { get; set; } = string.Empty;
        
        /// <summary>
        /// 缺省数据类型
        /// </summary>
        public string DefaultDataType { get; set; } = string.Empty;
    }

    /// <summary>
    /// 子表数据模型
    /// </summary>
    public class SubSheetData
    {
        /// <summary>
        /// 子表名称
        /// </summary>
        public string SheetName { get; set; } = string.Empty;
        
        /// <summary>
        /// 参数列表
        /// </summary>
        public List<MonitoringParameter> Parameters { get; set; } = new List<MonitoringParameter>();
    }

    /// <summary>
    /// HJ212编码设备Excel读取器
    /// </summary>
    public class DeviceHJ212Code
    {
        /// <summary>
        /// 全局存储所有子表数据
        /// </summary>
        private static List<SubSheetData> _allSheetData = new List<SubSheetData>();
        
        /// <summary>
        /// 是否已加载数据
        /// </summary>
        private static bool _isDataLoaded = false;
        
        /// <summary>
        /// 当前加载的文件路径
        /// </summary>
        private static string _currentFilePath = string.Empty;

        /// <summary>
        /// 加载状态
        /// </summary>
        private static bool _isLoading = false;

        /// <summary>
        /// 加载进度回调
        /// </summary>
        public static Action<string>? OnLoadingProgress { get; set; }

        /// <summary>
        /// 加载完成回调
        /// </summary>
        public static Action<bool, string>? OnLoadingCompleted { get; set; }

        /// <summary>
        /// 获取所有子表数据（只读）
        /// </summary>
        public static IReadOnlyList<SubSheetData> AllSheetData => _allSheetData.AsReadOnly();
        
        /// <summary>
        /// 获取是否已加载数据
        /// </summary>
        public static bool IsDataLoaded => _isDataLoaded;
        
        /// <summary>
        /// 获取当前加载的文件路径
        /// </summary>
        public static string CurrentFilePath => _currentFilePath;

        /// <summary>
        /// 获取是否正在加载
        /// </summary>
        public static bool IsLoading => _isLoading;

        /// <summary>
        /// 异步加载Excel文件到全局变量
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <returns>加载任务</returns>
        public static Task<bool> LoadAllSheetsAsync(string filePath)
        {
            return Task.Run(() => LoadAllSheets(filePath));
        }

        /// <summary>
        /// 后台异步加载Excel文件（带进度回调）
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <returns>加载任务</returns>
        public static async Task<bool> LoadAllSheetsWithProgressAsync(string filePath)
        {
            if (_isLoading)
            {
                OnLoadingProgress?.Invoke("已有加载任务正在进行中...");
                return false;
            }

            _isLoading = true;
            OnLoadingProgress?.Invoke("开始加载Excel文件...");

            try
            {
                // 判断文件是否存在
                if (!File.Exists(filePath))
                {
                    OnLoadingProgress?.Invoke("文件不存在");
                    OnLoadingCompleted?.Invoke(false, "文件不存在");
                    return false;
                }

                OnLoadingProgress?.Invoke("正在读取Excel文件...");
                
                // 设置EPPlus许可证模式
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                
                using (var package = new ExcelPackage(new FileInfo(filePath)))
                {
                    var worksheets = package.Workbook.Worksheets.ToList();
                    var totalSheets = worksheets.Count;
                    var loadedSheets = new List<SubSheetData>();
                    
                    OnLoadingProgress?.Invoke($"发现 {totalSheets} 个子表，开始处理...");
                    
                    for (int i = 0; i < worksheets.Count; i++)
                    {
                        var worksheet = worksheets[i];
                        OnLoadingProgress?.Invoke($"正在处理子表 {i + 1}/{totalSheets}: {worksheet.Name}");
                        
                        var sheetData = await Task.Run(() => ReadSheet(worksheet));
                        if (sheetData != null)
                        {
                            loadedSheets.Add(sheetData);
                        }
                        
                        // 模拟进度更新
                        await Task.Delay(10); // 避免UI卡顿
                    }
                    
                    // 更新全局数据
                    _allSheetData = loadedSheets;
                    _isDataLoaded = true;
                    _currentFilePath = filePath;
                    
                    OnLoadingProgress?.Invoke($"加载完成！共加载 {loadedSheets.Count} 个子表，{loadedSheets.Sum(s => s.Parameters.Count)} 个参数");
                    OnLoadingCompleted?.Invoke(true, $"成功加载 {loadedSheets.Count} 个子表");
                    
                    return true;
                }
            }
            catch (Exception ex)
            {
                OnLoadingProgress?.Invoke($"加载失败: {ex.Message}");
                OnLoadingCompleted?.Invoke(false, $"加载失败: {ex.Message}");
                _isDataLoaded = false;
                _currentFilePath = string.Empty;
                return false;
            }
            finally
            {
                _isLoading = false;
            }
        }

        /// <summary>
        /// 从Excel文件读取所有子表数据并存储到全局变量
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <returns>是否加载成功</returns>
        public static bool LoadAllSheets(string filePath)
        {
            try
            {
                // 判断文件是否存在
                if (!File.Exists(filePath))
                {
                    return false;
                }
                var result = ReadAllSheets(filePath);
                _allSheetData = result;
                _isDataLoaded = true;
                _currentFilePath = filePath;
                return true;
            }
            catch
            {
                _isDataLoaded = false;
                _currentFilePath = string.Empty;
                return false;
            }
        }

        /// <summary>
        /// 取消加载
        /// </summary>
        public static void CancelLoading()
        {
            if (_isLoading)
            {
                OnLoadingProgress?.Invoke("用户取消加载");
                _isLoading = false;
            }
        }

        /// <summary>
        /// 等待加载完成
        /// </summary>
        /// <param name="timeout">超时时间（毫秒）</param>
        /// <returns>是否加载完成</returns>
        public static async Task<bool> WaitForLoadingAsync(int timeout = 30000)
        {
            var startTime = DateTime.Now;
            while (_isLoading)
            {
                if ((DateTime.Now - startTime).TotalMilliseconds > timeout)
                {
                    return false;
                }
                await Task.Delay(100);
            }
            return _isDataLoaded;
        }

        /// <summary>
        /// 获取加载状态信息
        /// </summary>
        /// <returns>状态信息</returns>
        public static string GetLoadingStatus()
        {
            if (_isLoading)
                return "正在加载中...";
            else if (_isDataLoaded)
                return $"已加载 {_allSheetData.Count} 个子表";
            else
                return "未加载数据";
        }

        /// <summary>
        /// 根据子表名称获取子表内容
        /// </summary>
        /// <param name="sheetName">子表名称</param>
        /// <returns>子表数据，如果不存在则返回null</returns>
        public static SubSheetData? GetSheetByName(string sheetName)
        {
            if (!_isDataLoaded)
                return null;
                
            return _allSheetData.FirstOrDefault(sheet => 
                string.Equals(sheet.SheetName, sheetName, StringComparison.OrdinalIgnoreCase));
        }

        /// <summary>
        /// 获取所有子表名称列表
        /// </summary>
        /// <returns>子表名称列表</returns>
        public static List<string> GetSheetNameList()
        {
            if (!_isDataLoaded)
                return new List<string>();
                
            return _allSheetData.Select(sheet => sheet.SheetName).ToList();
        }

        /// <summary>
        /// 根据子表名称获取参数列表
        /// </summary>
        /// <param name="sheetName">子表名称</param>
        /// <returns>参数列表，如果子表不存在则返回空列表</returns>
        public static List<MonitoringParameter> GetParametersBySheetName(string sheetName)
        {
            var sheet = GetSheetByName(sheetName);
            return sheet?.Parameters ?? new List<MonitoringParameter>();
        }

        /// <summary>
        /// 根据编码获取参数信息
        /// </summary>
        /// <param name="code">编码</param>
        /// <param name="sheetName">子表名称（可选，如果不指定则搜索所有子表）</param>
        /// <returns>参数信息，如果不存在则返回null</returns>
        public static MonitoringParameter? GetParameterByCode(string code, string? sheetName = null)
        {
            if (!_isDataLoaded)
                return null;
                
            if (string.IsNullOrEmpty(sheetName))
            {
                // 搜索所有子表
                foreach (var sheet in _allSheetData)
                {
                    var parameter = sheet.Parameters.FirstOrDefault(p => 
                        string.Equals(p.Code, code, StringComparison.OrdinalIgnoreCase));
                    if (parameter != null)
                        return parameter;
                }
            }
            else
            {
                // 搜索指定子表
                var sheet = GetSheetByName(sheetName);
                if (sheet != null)
                {
                    return sheet.Parameters.FirstOrDefault(p => 
                        string.Equals(p.Code, code, StringComparison.OrdinalIgnoreCase));
                }
            }
            
            return null;
        }

        /// <summary>
        /// 清空全局数据
        /// </summary>
        public static void ClearData()
        {
            _allSheetData.Clear();
            _isDataLoaded = false;
            _currentFilePath = string.Empty;
        }

        /// <summary>
        /// 重新加载数据
        /// </summary>
        /// <returns>是否重新加载成功</returns>
        public static bool ReloadData()
        {
            if (string.IsNullOrEmpty(_currentFilePath))
                return false;
                
            return LoadAllSheets(_currentFilePath);
        }

        /// <summary>
        /// 获取数据统计信息
        /// </summary>
        /// <returns>统计信息字符串</returns>
        public static string GetDataStatistics()
        {
            if (!_isDataLoaded)
                return "未加载数据";
                
            var totalSheets = _allSheetData.Count;
            var totalParameters = _allSheetData.Sum(sheet => sheet.Parameters.Count);
            
            return $"已加载 {totalSheets} 个子表，共 {totalParameters} 个参数";
        }

        /// <summary>
        /// 从Excel文件读取所有子表数据
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <returns>所有子表数据</returns>
        public static List<SubSheetData> ReadAllSheets(string filePath)
        {
            var result = new List<SubSheetData>();
            
            // 设置EPPlus许可证模式
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            
            using (var package = new ExcelPackage(new FileInfo(filePath)))
            {
                foreach (var worksheet in package.Workbook.Worksheets)
                {
                    var sheetData = ReadSheet(worksheet);
                    if (sheetData != null)
                    {
                        result.Add(sheetData);
                    }
                }
            }
            
            return result;
        }

        /// <summary>
        /// 读取单个工作表
        /// </summary>
        /// <param name="worksheet">工作表</param>
        /// <returns>工作表数据</returns>
        private static SubSheetData? ReadSheet(ExcelWorksheet worksheet)
        {
            if (worksheet.Dimension == null) return null;
            
            var sheetData = new SubSheetData
            {
                SheetName = worksheet.Name
            };
            
            // 获取数据范围
            int rowCount = worksheet.Dimension.Rows;
            int colCount = worksheet.Dimension.Columns;
            
            // 从第二行开始读取数据（第一行是表头）
            for (int row = 2; row <= rowCount; row++)
            {
                var parameter = new MonitoringParameter
                {
                    Code = GetCellValue(worksheet, row, 1),
                    ChineseName = GetCellValue(worksheet, row, 2),
                    OriginalCode = GetCellValue(worksheet, row, 3),
                    DefaultUnit = GetCellValue(worksheet, row, 4),
                    DefaultMeasurement = GetCellValue(worksheet, row, 5),
                    DefaultDataType = GetCellValue(worksheet, row, 6)
                };
                
                // 只添加有编码的行
                if (!string.IsNullOrWhiteSpace(parameter.Code))
                {
                    sheetData.Parameters.Add(parameter);
                }
            }
            
            return sheetData.Parameters.Count > 0 ? sheetData : null;
        }

        /// <summary>
        /// 获取单元格值
        /// </summary>
        /// <param name="worksheet">工作表</param>
        /// <param name="row">行号</param>
        /// <param name="col">列号</param>
        /// <returns>单元格值</returns>
        private static string GetCellValue(ExcelWorksheet worksheet, int row, int col)
        {
            var cell = worksheet.Cells[row, col];
            return cell.Value?.ToString() ?? string.Empty;
        }

        /// <summary>
        /// 根据子表名称读取特定子表
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <param name="sheetName">子表名称</param>
        /// <returns>子表数据</returns>
        public static SubSheetData? ReadSpecificSheet(string filePath, string sheetName)
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            
            using (var package = new ExcelPackage(new FileInfo(filePath)))
            {
                var worksheet = package.Workbook.Worksheets.FirstOrDefault(w => w.Name == sheetName);
                if (worksheet != null)
                {
                    return ReadSheet(worksheet);
                }
            }
            
            return null;
        }

        /// <summary>
        /// 获取所有子表名称
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <returns>子表名称列表</returns>
        public static List<string> GetSheetNames(string filePath)
        {
            var sheetNames = new List<string>();
            
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            
            using (var package = new ExcelPackage(new FileInfo(filePath)))
            {
                foreach (var worksheet in package.Workbook.Worksheets)
                {
                    sheetNames.Add(worksheet.Name);
                }
            }
            
            return sheetNames;
        }

        /// <summary>
        /// 验证Excel文件格式是否正确
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <returns>验证结果</returns>
        public static bool ValidateExcelFormat(string filePath)
        {
            try
            {
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                
                using (var package = new ExcelPackage(new FileInfo(filePath)))
                {
                    if (package.Workbook.Worksheets.Count == 0)
                        return false;
                    
                    // 检查第一个工作表是否有正确的列结构
                    var firstSheet = package.Workbook.Worksheets.First();
                    if (firstSheet.Dimension == null || firstSheet.Dimension.Columns < 6)
                        return false;
                    
                    // 检查表头是否正确
                    var expectedHeaders = new[] { "编码", "中文名称", "原编码", "缺省计量单位", "缺省计量", "缺省数据类型" };
                    for (int i = 0; i < expectedHeaders.Length; i++)
                    {
                        var headerValue = GetCellValue(firstSheet, 1, i + 1);
                        if (headerValue != expectedHeaders[i])
                            return false;
                    }
                    
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 导出数据到Excel文件
        /// </summary>
        /// <param name="filePath">输出文件路径</param>
        /// <param name="sheetDataList">要导出的数据</param>
        public static void ExportToExcel(string filePath, List<SubSheetData> sheetDataList)
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            
            using (var package = new ExcelPackage())
            {
                foreach (var sheetData in sheetDataList)
                {
                    var worksheet = package.Workbook.Worksheets.Add(sheetData.SheetName);
                    
                    // 写入表头
                    var headers = new[] { "编码", "中文名称", "原编码", "缺省计量单位", "缺省计量", "缺省数据类型" };
                    for (int i = 0; i < headers.Length; i++)
                    {
                        worksheet.Cells[1, i + 1].Value = headers[i];
                    }
                    
                    // 写入数据
                    for (int row = 0; row < sheetData.Parameters.Count; row++)
                    {
                        var param = sheetData.Parameters[row];
                        worksheet.Cells[row + 2, 1].Value = param.Code;
                        worksheet.Cells[row + 2, 2].Value = param.ChineseName;
                        worksheet.Cells[row + 2, 3].Value = param.OriginalCode;
                        worksheet.Cells[row + 2, 4].Value = param.DefaultUnit;
                        worksheet.Cells[row + 2, 5].Value = param.DefaultMeasurement;
                        worksheet.Cells[row + 2, 6].Value = param.DefaultDataType;
                    }
                    
                    // 自动调整列宽
                    worksheet.Cells.AutoFitColumns();
                }
                
                package.SaveAs(new FileInfo(filePath));
            }
        }
    }
}
